{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks = spark.read.format(\"csv\").options(header = True, inferSchema = True).load(\"stocks\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---------+---------+---------+---------+---------+---------+------+\n",
      "|                date|     open|     high|      low|    close|   volume| adjclose|symbol|\n",
      "+--------------------+---------+---------+---------+---------+---------+---------+------+\n",
      "|2000-07-17 00:00:...|  95.4375|     97.5|    92.75|   96.625|3508100.0|74.269199|  XLNX|\n",
      "|2000-07-17 00:00:...|   22.625|    22.75|  22.4375|  22.5625| 201600.0| 13.48614|    ES|\n",
      "|2000-07-17 00:00:...| 6.750002| 6.937503|    6.375|      6.5|1235700.0| 5.241649|   CHK|\n",
      "|2000-07-17 00:00:...|19.812501|  20.1875|19.500001|  20.1875|1434100.0| 3.806147|    NI|\n",
      "|2000-07-17 00:00:...|     30.5|  30.6875|     30.0| 30.03125| 254600.0| 19.81183|   SNA|\n",
      "|2000-07-17 00:00:...|44.749996|45.062498|44.500004|45.000009| 535200.0|17.400773|  FOXA|\n",
      "|2000-07-17 00:00:...|   19.625|   19.625|    19.25|   19.375| 309500.0|13.768835|     R|\n",
      "|2000-07-17 00:00:...|  16.6562|  16.6875|   16.125|    16.25|5507200.0| 1.755466|  ROST|\n",
      "|2000-07-17 00:00:...|    56.25|    57.25|  56.0625|   56.125|7941200.0| 18.31076|    PG|\n",
      "|2000-07-17 00:00:...|54.000326|54.000326|52.500318|53.375325|3725000.0|71.068871|   TYC|\n",
      "|2000-07-17 00:00:...|    58.75|   58.875|  57.8125|     58.0| 182700.0|37.544123|    XL|\n",
      "|2000-07-17 00:00:...|47.500132|47.500132|45.750135|46.343886|4898700.0|17.662922|     F|\n",
      "|2000-07-17 00:00:...|     84.0|     84.5|   82.625|82.671883|2861800.0| 23.88973|   CVX|\n",
      "|2000-07-17 00:00:...|     22.5|    22.75|   22.375|    22.75| 423600.0| 5.942444|   PPL|\n",
      "|2000-07-17 00:00:...|  37.4375|  37.5625|  36.5625|  37.4375| 738800.0|24.832407|   TRV|\n",
      "|2000-07-17 00:00:...|76.874999|80.937504|76.249993|78.250003|6166200.0| 50.37851|     A|\n",
      "|2000-07-17 00:00:...|     26.5|  26.6875|  26.3125|     26.5| 335200.0| 6.240835|   LNT|\n",
      "|2000-07-17 00:00:...|  23.9375|  24.0625|     23.5|  23.9375| 648400.0|  23.9375|   AZO|\n",
      "|2000-07-17 00:00:...|   60.875|  60.9375|    60.25|60.531239|1464800.0|22.017028|   UTX|\n",
      "|2000-07-17 00:00:...|   3.5625| 3.625005| 3.312495| 3.437505| 340000.0|  2.20411|   RRC|\n",
      "+--------------------+---------+---------+---------+---------+---------+---------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "stocks.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "stocks.createOrReplaceTempView(\"stocks\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "stocks_agg = spark.sql(\"\"\"\n",
    "select symbol, avg(volume) avg_vol \n",
    "    from stocks \n",
    "where year(date) = 2016 group by symbol \n",
    "order by avg_vol desc\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+--------------------+\n",
      "|symbol|             avg_vol|\n",
      "+------+--------------------+\n",
      "|   BAC|1.0995368974358974E8|\n",
      "|   FCX|4.7979558333333336E7|\n",
      "|   CHK|4.1622735256410256E7|\n",
      "|  AAPL|4.0944183974358976E7|\n",
      "|    GE|3.7751663461538464E7|\n",
      "|     F| 3.743219743589743E7|\n",
      "|   PFE|3.5777183974358976E7|\n",
      "|  MSFT| 3.419444807692308E7|\n",
      "|    FB|2.8902566025641024E7|\n",
      "|    MU|2.7260807692307692E7|\n",
      "|    AA|2.6758177564102564E7|\n",
      "|   MRO| 2.649072564102564E7|\n",
      "|  CSCO|2.6301159615384616E7|\n",
      "|  INTC|2.4185176282051284E7|\n",
      "|     T| 2.371588846153846E7|\n",
      "|     C| 2.338251282051282E7|\n",
      "|   KMI|2.1680207692307692E7|\n",
      "|    RF|2.0861117307692308E7|\n",
      "|   WFC|1.9612389743589744E7|\n",
      "|   SWN|1.8398848717948716E7|\n",
      "+------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "stocks_agg.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "stocks_agg.coalesce(1).write.format(\"json\").mode(\"overwrite\").save(\"stocks-summary\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(\"Job is complete\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.4.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
